package cn.chinaflame.pagodas.repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;

import cn.chinaflame.pagodas.entity.PaProductPoint;

public interface PaProductPointRepository extends JpaRepository<PaProductPoint, String>, JpaSpecificationExecutor<PaProductPoint>{

//	@Query("select c from PaProductPoint c where c.status=?1"
//			+" and c.positionId=?2 and c.vipFlg=?3 order by CAST(c.row AS SIGNED),CAST(c.col AS SIGNED) desc limit 0,1")
//	public PaProductPoint findLight(String status, String positionId, String vipFlg);
	@Query("select c from PaProductPoint c where c.orderitemId = ?1")
	public List<PaProductPoint> findByOrderitemId(String orderitemId);
	@Query("select c from PaProductPoint c where c.positionId = ?1 and c.status <> ?2")
	public List<PaProductPoint> findByPositionIdAndNonStatus(String positionId, String status);

	@Query("select c from PaProductPoint c where c.positionId = ?1 and c.orderitemId = ?2 and c.locationNo = ?3")
	public PaProductPoint findAllByPositionIdAndOrderitemIdAndLocationNo (String positionId,String orderitemId,String locationNo);

	@Query("select c from PaProductPoint c where c.orderitemId = ?1 and c.locationNo = ?2 and c.status = ?3")
	public PaProductPoint findByOrderitemIdAndLocationNoAndStatus(String orderitemId,String locationNo, String status);

	@Query("select max(c.row) as row, max(c.col) as col from PaProductPoint c where position_id = ?1")
	public Map<String, String> findMaxrowMaxcol(String positionId);
	
	/**
	 * 查询位置总行数
	 * @param positionId
	 * @return
	 */
	@Query("select count(distinct c.row) from PaProductPoint c where c.positionId = ?1")
	public int findByRowCountByPositionId(String positionId);
	@Modifying
	@Query("update PaProductPoint c set c.status = ?1 where c.orderitemId = ?2")
	public int updateByStatusAndOrderitemId(String status, String orderitemId);
	@Modifying
	@Query("update PaProductPoint c set c.status = ?1,c.orderitemId = ?2 where c.locationNo = ?3")
	public int updateByStatusAndOrderitemIdByLocaNo(String status, String orderitemId, String locationNo);
	
	/**
	 * 
	 * @param lights
	 */
/*	public void exculdeLights(List<String> lights) {
		String sql ="update pa_product_point set status=0 where location_no=?";
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
			@Override
			public int getBatchSize() {
				return lights.size();
			}
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				String lightno = lights.get(i);
				ps.setString(1, lightno);
			}
		  });
	}*/
}
